package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.UserDto;

public class UserDaoImp extends ViewListDao implements UserDao {

	protected UserDaoImp() throws TmsDaoException {
		super();
	}
	protected UserDaoImp(int pageNo,
			int maxIndex,int sortKey,boolean asc) throws TmsDaoException{
		super( pageNo,maxIndex, sortKey, asc);
	}
	protected final String[] userAllList = new String[]{"USERID","USERID","REALNAME","INSERTTIME","USERTYPE","FLAG"};
	/**
	 * 验证是否可登陆
	 */
	@Override
	public UserDto findUserProfileByLoginid(String loginId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("REALNAME,");
		sql.append("UCID, ");
		sql.append("FLAG, ");
		sql.append("USERTYPE ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append("   WHERE USERID =? ");
		UserDto userprofile = null;
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, loginId);
			rs = ps.executeQuery();
			while (rs.next()) {
				userprofile = new UserDto();
				userprofile.setId(rs.getLong("ID"));
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setRealName(rs.getString("REALNAME"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setFlag(rs.getInt("FLAG"));
				userprofile.setUserType(rs.getInt("USERTYPE"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return userprofile;
	}

	/**
	 *  单条插入用户信息
	 * @throws TmsDaoException 
	 */
	@Override
	public void saveUserInfo(UserDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append("(");
			sql.append(" USERID, ");
			sql.append(" REALNAME, ");
			sql.append(" PASSWORD, ");
			sql.append(" UCID, ");
			sql.append(" UPDATETIME, ");
			sql.append(" INSERTTIME, ");
			sql.append(" UPDATEUSERID, ");
			sql.append(" FLAG, ");
			sql.append(" INSERTUSERID, ");
			sql.append(" USERTYPE");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,SYSDATE(),SYSDATE(),?,1,?,? )");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, dto.getUserName());
			ps.setString(2, dto.getRealName());
			ps.setString(3, dto.getPassword());
			ps.setInt(4, dto.getUcId());
			ps.setInt(5, dto.getUpdateUserId());
			ps.setInt(6, dto.getInsertUserId());
			ps.setInt(7, dto.getUserType());
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	
	/**
	 * 查询用户列表
	 * @throws TmsDaoException 
	 */
	@Override
	public List<UserDto> getAllUserInfo(int userClass ,int uniqueid) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<UserDto> list = new ArrayList<UserDto>();
		
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME, ");
		sql.append("INSERTTIME, ");
		sql.append("UPDATETIME, ");
		sql.append(" FLAG ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" WHERE (FLAG=1 OR FLAG=3) ");
		sql.append(" AND USERID <> 'root' ");
		//排序  INSERTTIME
		sql.append(getOrderBySql(userAllList));
		UserDto userprofile = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				userprofile = new UserDto();
				userprofile.setId(rs.getLong("ID"));
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setRealName(rs.getString("REALNAME"));
				userprofile.setFlag(rs.getInt("FLAG"));
				userprofile.setInsertTime(rs.getTimestamp("INSERTTIME"));
				userprofile.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				list.add(userprofile);
			}
			
			return list;

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public UserDto findUserInfoByUserId(String userId,int id) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append("   WHERE USERID =? ");
		if(id>0)
		{
			sql.append("   AND ID <> "+id+"  ");
		}

		UserDto userprofile = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			sql.delete(0, sql.length());
			ps.setString(1, userId);
			
			rs = ps.executeQuery();

			while (rs.next()) {
				userprofile = new UserDto();
				userprofile.setId(rs.getLong("ID"));
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setRealName(rs.getString("REALNAME"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return userprofile;
	}

	@Override
	public UserDto findUserInfoById(long id) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME, ");
		sql.append("USERTYPE, ");
		sql.append("INSERTTIME, ");
		sql.append("FLAG ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append("   WHERE ID =? ");

		UserDto userprofile = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			sql.delete(0, sql.length());
			ps.setLong(1, id);

			rs = ps.executeQuery();

			while (rs.next()) {
				userprofile = new UserDto();
				userprofile.setId(id);
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setRealName(rs.getString("REALNAME"));
				userprofile.setUserType(rs.getInt("USERTYPE"));
				userprofile.setInsertTime(rs.getTimestamp("INSERTTIME"));
				userprofile.setFlag(rs.getInt("FLAG"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return userprofile;
	}
	/**
	 * 根据用户id和密码查询用户信息
	 */

	@Override
	public UserDto findPasswordById(int id, String password) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append("   WHERE ID =? ");
		sql.append("   AND PASSWORD =? ");

		UserDto userprofile = null;
		try {

			ps = conn.prepareStatement(sql.toString());
			sql.delete(0, sql.length());
			ps.setLong(1, id);
			ps.setString(2, password);

			rs = ps.executeQuery();

			while (rs.next()) {
				userprofile = new UserDto();
				userprofile.setId(id);
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setRealName(rs.getString("REALNAME"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return userprofile;
	}

	/**
	 * 修改用户密码
	 * @throws TmsDaoException 
	 */
	@Override
	public void updatePasswordById(int id,String newPassword) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" PASSWORD=?  ");
			sql.append(" WHERE  ");
			sql.append(" ID=? ");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, newPassword);
			ps.setLong(2, id);

			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}	
	}
	/**
	 * 修改用户密码
	 * @throws TmsDaoException 
	 */
	@Override
	public void updatePasswordInfoById(int id,String newPassword,int updateUserId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" PASSWORD=?,  ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=? ");
			sql.append(" WHERE  ");
			sql.append(" ID=? ");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setString(1, newPassword);
			ps.setInt(2, updateUserId);
			ps.setLong(3, id);

			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}	
	}

	/**
	 * 修改用户信息
	 * @throws TmsDaoException 
	 */
	@Override
	public void updateUserInfoById(UserDto dto) throws TmsDaoException {

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//去唯一性 每次修改之前先查下当前数据是否有更改 有的话抛出异常给出提示
			
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" UCID=?, ");
			sql.append(" UPDATETIME=SYSDATE(), ");
			sql.append(" UPDATEUSERID=? ");
			sql.append(" WHERE ");
			sql.append(" ID=? ");

			ps = conn.prepareStatement(sql.toString());
			
			ps.setInt(1, dto.getUcId());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setLong(3, dto.getId());

			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}

	/**
	 * 根据主键ID删除用户信息
	 * 
	 * 逻辑删除
	 * 
	 * @throws TmsDaoException 
	 */
	@Override
	public void deleteUserInfoById(int id) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 删除语句
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" FLAG=2 ");
			sql.append(" WHERE ID = ? ");
			//
			ps = conn.prepareStatement(sql.toString());
			// 主键id
			ps.setInt(1, id);
			// 执行
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}		
	}

	
	public void updatePasswordByUserId(String password,String userId,int updateUserId) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 删除语句
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" PASSWORD=?, ");
			sql.append("UPDATEUSERID=?,");
			sql.append("UPDATETIME=SYSDATE() ");
			sql.append(" WHERE USERID = ? ");
			//
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, password);
			ps.setInt(2, updateUserId);
			ps.setString(3, userId);
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
	}
	
	/**
	 * 
	 * 根据会员登录名修改帐号状态
	 */
	@Override
	public void updateLoginStatusByUserId(UserDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 删除语句
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" FLAG=?, ");
			sql.append("UPDATEUSERID=?,");
			sql.append("UPDATETIME=SYSDATE() ");
			sql.append(" WHERE USERID = ? ");
			//
			ps = conn.prepareStatement(sql.toString());
			// 主键id
			ps.setInt(1, dto.getFlag());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setString(3, dto.getUserName());
			// 执行
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
	}

	/***
	 * 查询所有状态为1的用户信息
	 */
	@Override
	public  List<UserDto>  findIpadUser() throws TmsDaoException{

		//开始掉数据		
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append("   WHERE UCID = 51  ");
		sql.append("   AND FLAG<> 3  ");
		
		List<UserDto> userprofile = new ArrayList<UserDto>();
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();

		while (rs.next()) {
			UserDto user = new UserDto();
			user.setId(rs.getLong("ID"));
			user.setUserName(rs.getString("USERID"));
			user.setPassword(rs.getString("PASSWORD"));
			user.setUcId(rs.getInt("UCID"));
			user.setRealName(rs.getString("REALNAME"));
			userprofile.add(user);
		}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		return userprofile;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List findUserByUcId(int ucId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List userList = new ArrayList();
		try {
			
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("ID, ");
			sql.append("USERID, ");
			sql.append("PASSWORD, ");
			sql.append("UCID, ");
			sql.append("REALNAME ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append("   WHERE UCID = ?  ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt( 1,ucId);
			rs = ps.executeQuery();
						
			while(rs.next()){
				UserDto user = new UserDto();
				user.setId(rs.getLong("ID"));
				user.setUserName(rs.getString("USERID"));
				user.setPassword(rs.getString("PASSWORD"));
				user.setUcId(rs.getInt("UCID"));
				user.setRealName(rs.getString("REALNAME"));
				userList.add(user);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
		return userList;
	}

	/**
	 * 会员和经办人部分更新登录信息
	 */
	@Override
	public void updateLoginInfoAtAttn(UserDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 删除语句
			StringBuffer sql = new StringBuffer("UPDATE ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append(" SET ");
			sql.append(" REALNAME=?, ");
			sql.append(" UPDATEUSERID=?, ");
			sql.append(" UPDATETIME=SYSDATE() ");
			sql.append(" WHERE USERID = ? ");
			//
			ps = conn.prepareStatement(sql.toString());
			// 主键id
			ps.setString(1, dto.getRealName());
			ps.setInt(2, dto.getUpdateUserId());
			ps.setString(3, dto.getUserName());
			// 执行
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}			
	}
	@Override
	public List findCyUserByUcId(int ucId, int roleId) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List userList = new ArrayList();
		try {
			
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("USER.ID AS ID, ");
			sql.append("USER.USERID AS USERID, ");
			sql.append("USER.PASSWORD AS PASSWORD, ");
			sql.append("USER.UCID AS UCID, ");
			sql.append("USER.REALNAME AS REALNAME  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append("   USER  ");
			sql.append("   INNER JOIN   ");
			sql.append(TmsTableNames.TMS_USER_ROLE);
			sql.append("   R ON R.UCID=USER.UCID AND  R.ROLE="+roleId+" ");
			sql.append("   INNER JOIN   ");
			sql.append(TmsTableNames.TMS_USER_AUTH);
			sql.append("   AUTH  ");
			sql.append("   ON AUTH.USERID=USER.ID AND AUTH.RID=R.ROLE    ");
			sql.append(" WHERE USER.UCID = ? ");
			ps = conn.prepareStatement(sql.toString());
			ps.setInt( 1,ucId);
			rs = ps.executeQuery();
						
			while(rs.next()){
				UserDto user = new UserDto();
				user.setId(rs.getLong("ID"));
				user.setUserName(rs.getString("USERID"));
				user.setPassword(rs.getString("PASSWORD"));
				user.setUcId(rs.getInt("UCID"));
				user.setRealName(rs.getString("REALNAME"));
				userList.add(user);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		return userList;
	}
	@Override
	public List<UserDto> getUserListByRole() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<UserDto> userList = new ArrayList<UserDto>();
		try {
			StringBuffer sql = new StringBuffer("SELECT ");
			sql.append("DISTINCT U.ID AS ID,    ");
			sql.append("U.REALNAME AS REALNAME  ");
			sql.append("FROM ");
			sql.append(TmsTableNames.TMS_USER);
			sql.append("   U  ");
			sql.append("   INNER JOIN  ");
			sql.append(TmsTableNames.TMS_USER_AUTH);
			sql.append("   AUTH ON U.ID=AUTH.USERID   ");
			sql.append(" AND U.FLAG=1");
			sql.append("   AND (AUTH.RID=480 OR AUTH.RID=510 OR AUTH.RID=500)  ");
			
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				UserDto user = new UserDto();
				user.setId(rs.getLong("ID"));
				user.setRealName(rs.getString("REALNAME"));
				userList.add(user);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}	
		
		return userList;
	}
	@Override
	public boolean isRepeatUserId(String userId) throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);
		sql.append(" WHERE USERID =? ");
		sql.append(" AND (FLAG = 1 OR FLAG = 3)");

		boolean result = false;
		try {

			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, userId);
			
			rs = ps.executeQuery();

			while (rs.next()) {
				result = true;
			}
			return result;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	
	
	@Override
	public List<UserDto> findAllUser() throws TmsDaoException {
		// 开始掉数据
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append("ID, ");
		sql.append("USERID, ");
		sql.append("PASSWORD, ");
		sql.append("UCID, ");
		sql.append("REALNAME, ");
		sql.append("USERTYPE, ");
		sql.append("INSERTTIME, ");
		sql.append("FLAG ");
		sql.append("FROM ");
		sql.append(TmsTableNames.TMS_USER);

		List<UserDto> list = new ArrayList<UserDto>();
		try {
			ps = conn.prepareStatement(sql.toString());
			sql.delete(0, sql.length());
			rs = ps.executeQuery();
			while (rs.next()) {
				UserDto userprofile = new UserDto();
				userprofile.setId(rs.getLong("ID"));
				userprofile.setUserName(rs.getString("USERID"));
				userprofile.setPassword(rs.getString("PASSWORD"));
				userprofile.setUcId(rs.getInt("UCID"));
				userprofile.setRealName(rs.getString("REALNAME"));
				userprofile.setUserType(rs.getInt("USERTYPE"));
				userprofile.setInsertTime(rs.getTimestamp("INSERTTIME"));
				userprofile.setFlag(rs.getInt("FLAG"));
				list.add(userprofile);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return list;
	}
}
